﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Office.Interop.Excel;
using Spire.Xls;
using Workbook = Microsoft.Office.Interop.Excel.Workbook;
using Worksheet = Microsoft.Office.Interop.Excel.Worksheet;

namespace YanTableManger
{
    /// <summary>
    /// Excel 表格操作类
    /// </summary>
    class ExcelManager
    {
        /// <summary>
        /// 已启动的Excel程序
        /// </summary>
        public Application Excel { get; set; }

        /// <summary>
        /// 所有工作簿
        /// </summary>
        public Workbooks Workbooks { get; set; }

        /// <summary>
        /// 正在编辑的工作簿
        /// </summary>
        public Workbook Workbook { get; set; }

        /// <summary>
        /// 正在编辑的工作簿中的工作表
        /// </summary>
        public Worksheets Worksheets { get; set; }

        /// <summary>
        /// 正在编辑的工作表
        /// </summary>
        public Worksheet Worksheet { get; set; }

        /// <summary>
        /// 文件名称
        /// </summary>
        public string FileName { get; set; }

        /// <summary>
        /// 缺失的类型值
        /// </summary>
        private static object _missing = Missing.Value;

        public ExcelManager()
        {
            Excel = new Application();
            Workbooks = Excel.Workbooks;
        }

        /// <summary>
        /// 设置显示弹窗警告
        /// </summary>
        /// <param name="t"></param>
        public void SetDisplayAlerts(bool t)
        {
            Excel.Application.DisplayAlerts = t;
        }

        /// <summary>
        /// 创建一个工作簿
        /// </summary>
        public void CreateWorkbook()
        {
            Workbook = Workbooks.Add(true);
            OpenWorksheet("Sheet1");
        }

        /// <summary>
        /// 创建一个工作表
        /// </summary>
        public void CreateWorksheet()
        {
            try
            {
                Worksheet = Workbook.Worksheets.Add(_missing, _missing, _missing, _missing);
            }
            catch (Exception e)
            {
                Debug.WriteLine(e);
            }
        }

        /// <summary>
        /// 打开一个工作簿
        /// </summary>
        /// <param name="fileName">工作簿名称</param>
        public void OpenWorkbook(string fileName)
        {
            Workbook = Workbooks.Open(fileName, _missing, _missing, _missing, _missing, _missing, _missing, _missing,
                _missing,
                _missing, _missing, _missing, _missing, _missing, _missing);
            FileName = fileName;
        }

        /// <summary>
        /// 打开一个工作表
        /// </summary>
        /// <param name="name">工作表名称</param>
        public void OpenWorksheet(string name)
        {
            Worksheet = (Worksheet) Workbook.Worksheets[name];
        }

        /// <summary>
        /// 打开一个工作表,索引从1开始
        /// </summary>
        /// <param name="index">工作表索引，从1开始</param>
        public void OpenWorksheet(int index)
        {
            Worksheet = (Worksheet) Workbook.Worksheets.Item[index];
        }

        /// <summary>
        /// 保存文件到指定路径
        /// </summary>
        /// <param name="filePath">保存路径</param>
        /// <param name="fileName">文件名称</param>
        public void SaveCopyTo(string filePath, string fileName)
        {
            filePath = filePath.EndsWith("\\") ? filePath : "\\" + filePath;
            Workbook.SaveCopyAs(filePath + fileName + ".xlsx");
        }

        /// <summary>
        /// 保存当前的工作簿内容
        /// </summary>
        public void SaveWorkbook()
        {
            Workbook.Save();
        }

        /// <summary>
        /// 保存工作表
        /// </summary>
        public void SaveWorksheet()
        {
            Worksheet.SaveAs(FileName, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing,
                _missing);
        }

        /// <summary>
        /// 保存当前工作簿到指定文件
        /// </summary>
        /// <param name="fileName">文件路径名</param>
        public void SaveCopyAs(string fileName)
        {
            Workbook.SaveCopyAs(fileName);
        }

        /// <summary>
        /// 关闭工作簿
        /// </summary>
        public void CloseWorkbook()
        {
            Workbook.Close(false, _missing, _missing);
        }

        /// <summary>
        /// 关闭所有excel程序
        /// </summary>
        public static void Close()
        {
            try
            {
                Process[] excelProcess = Process.GetProcessesByName("EXCEL");
                if (excelProcess.Length == 0) return;
                foreach (var process in excelProcess)
                {
                    process.Kill();
                }
            }
            catch (Exception e)
            {
                // ignored
            }
        }

        /// <summary>
        /// 删除工作表
        /// </summary>
        /// <param name="name"></param>
        public void DeleteWorksheet(string name)
        {
            ((Worksheet) Workbook.Sheets[name]).Delete();
        }

        /// <summary>
        /// 设置单元格内容
        /// </summary>
        /// <param name="x">单元格横坐标</param>
        /// <param name="y">纵坐标</param>
        /// <param name="content">内容</param>
        public void SetCell(int x, int y, string content)
        {
            Worksheet.Cells[x, y] = content;
        }

        /// <summary>
        /// 设置单元格内容
        /// </summary>
        /// <param name="cell">单元格</param>
        public void SetCell(Cell cell)
        {
            Worksheet.Cells[cell.X, cell.Y] = cell.Content;
        }

        /// <summary>
        /// 批量设置单元格内容
        /// </summary>
        /// <param name="cells"></param>
        public void SetCells(List<Cell> cells)
        {
            foreach (var cell in cells)
            {
                SetCell(cell);
            }
        }

        /// <summary>
        /// 获取excel表格指定位置的数据
        /// </summary>
        /// <param name="pos">位置字符串，例如"A1";"B2:C8";</param>
        /// <returns></returns>
        public string GetData(string pos)
        {
            return Worksheet.Range[pos].Value.ToString();
        }

        /// <summary>
        /// 获取工作表行数
        /// </summary>
        /// <returns></returns>
        public int GetWorkSheetRowsCount()
        {
            return Worksheet.UsedRange.Rows.Count;
        }

        /// <summary>
        /// 获取工作表列数
        /// </summary>
        /// <returns></returns>
        public int GetWorkSheetColumnsCount()
        {
            return Worksheet.UsedRange.Columns.Count;
        }

        /// <summary>
        /// 设置一行数据,索引从1开始
        /// </summary>
        /// <param name="index"></param>
        /// <param name="contents"></param>
        public void SetRow(int index, List<object> contents)
        {
            for (var i = 0; i < contents.Count; i++)
            {
                Worksheet.Cells[index, i + 1] = contents[i];
            }
        }

        /// <summary>
        /// 设置单元格文本水平对齐方式
        /// </summary>
        /// <param name="range">起始单元格</param>
        /// <param name="type">类型</param>
        /// <param name="to">终止单元格</param>
        public void SetDataPosition(string range, HorizontalAlignType type)
        {
            Worksheet.Range[$"{range}"].Style.HorizontalAlignment = type;
        }

        public void SetFullDataPosition(HorizontalAlignType type)
        {
            SetDataPosition(GetFullRange(),type);
        }

        /// <summary>
        /// 设置多行单元格内容,索引从1开始
        /// </summary>
        /// <param name="begin"></param>
        /// <param name="datas"></param>
        public void SetRows(int begin, List<List<object>> datas)
        {
            for (var i = 0; i < datas.Count; i++)
            {
                SetRow(begin + i, datas[i]);
            }
        }

        /// <summary>
        /// 创意一个excel 工作簿并保存
        /// </summary>
        /// <param name="fileName">工作簿完整路径 </param>
        public void CreateExcelAddSave(string fileName)
        {
            CreateWorkbook();
            CreateWorksheet();
            SaveCopyAs(fileName);
            Close();
        }

        ///// <summary>
        ///// 使单元格自适应大小
        ///// </summary>
        //public void AutoFitSize()
        //{
        //    Worksheet.Range[GetFullRange()].AutoFit();
        //}

        /// <summary>
        /// 设置列宽,索引从1开始
        /// </summary>
        /// <param name="index">列索引</param>
        /// <param name="width">宽度</param>
        public void SetColumnWidth(int index, int width)
        {
            Worksheet.Columns[index].ColumnWidth = width;
        }
        /// <summary>
        /// 设置行高，索引从1开始
        /// </summary>
        /// <param name="index"></param>
        /// <param name="height"></param>
        public void SetRowHeight(int index, int height)
        {
            Worksheet.Rows[index].RowHeight = height;
        }

        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="leftTop">左上角单元格名称</param>
        /// <param name="rightBottom">右下角单元格名称</param>
        public void MergeCell(string leftTop, string rightBottom)
        {
            Worksheet.Range[leftTop, rightBottom].Merge(Worksheet.Range[leftTop, rightBottom].MergeCells);
        }

        /// <summary>
        /// 获取一行的所有单元格内容
        /// </summary>
        /// <param name="index">行号索引</param>
        /// <returns></returns>
        public List<string> GetRow(int index)
        {
            var res = new List<string>();
            var count = GetWorkSheetColumnsCount();
            for (var i = 0; i < count; i++)
            {
                res.Add(GetData(index, i));
            }

            return res;
        }

        /// <summary>
        /// 获取指定坐标的单元格内容，索引均从(0,0)开始
        /// </summary>
        /// <param name="x"></param>
        /// <param name="y"></param>
        /// <returns></returns>
        public string GetData(int x, int y)
        {
            var pos = GetCellName(x, y);
            return Worksheet.Range[pos].Value?.ToString();
        }

        /// <summary>
        /// 获取工作表中全部内容
        /// </summary>
        /// <returns></returns>
        public string[][] GetAllData()
        {
            var row = GetWorkSheetRowsCount();
            var col = GetWorkSheetColumnsCount();
            var res = new string[row][];
            for (int i = 0; i < row; i++)
            {
                res[i] = new string[col];
                for (int j = 0; j < col; j++)
                {
                    res[i][j] = GetData(i, j);
                }
            }

            return res;
        }
        /// <summary>
        /// 获取表格中指定列的全部内容，索引从0开始
        /// </summary>
        /// <param name="index"></param>
        /// <returns></returns>
        public string[] GetColumnData(int index)
        {
            var row = GetWorkSheetRowsCount();
            var res = new string[row];
            for (int i = 0; i < row; i++)
            {
                res[i] = GetData(i, index);
            }

            return res;
        }

        /// <summary>
        /// 获取表格中指定行的全部内容，索引从0开始
        /// </summary>
        /// <param name="index"></param>
        /// <returns></returns>
        public string[] GetRowData(int index)
        {
            var col = GetWorkSheetColumnsCount();
            var res = new string[col];
            for (int i = 0; i < col; i++)
            {
                res[i] = GetData(index, i);
            }

            return res;
        }

        /// <summary>
        /// 通过单元格坐标获取单元格名称
        /// </summary>
        /// <param name="x">行坐标，从0开始</param>
        /// <param name="y">列坐标，从0开始</param>
        /// <returns></returns>
        public string GetCellName(int x, int y)
        {
            x++;
            const string BAS = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
            var a = y / 26;
            var b = y % 26;
            var col = (a > 0 && a < 26) ? BAS.Substring(a - 1, 1) + BAS.Substring(b, 1) : BAS.Substring(b, 1);
            var pos = $"{col}{x}";
            return pos;
        }

        /// <summary>
        /// 通过范围的起始坐标和终点坐标获取范围名称
        /// </summary>
        /// <param name="fx">起始行坐标，从0开始</param>
        /// <param name="fy">起始列坐标，从0开始</param>
        /// <param name="tx">终止行坐标，从0开始</param>
        /// <param name="ty">终止列坐标，从0开始</param>
        /// <returns></returns>
        public string GetRangeName(int fx, int fy, int tx, int ty)
        {
            return GetCellName(fx, fy) + ":" + GetCellName(tx, ty);
        }
        /// <summary>
        /// 获取表格所有范围的名称
        /// </summary>
        /// <returns></returns>
        public string GetFullRange()
        {
            return GetRangeName(0, 0, GetWorkSheetRowsCount() - 1, GetWorkSheetColumnsCount() - 1);
        }
        /// <summary>
        /// 更改工作表名称
        /// </summary>
        /// <param name="name"></param>
        public void ChangeWorksheetName(string name)
        {
            Worksheet.Name = name;
        }
    }

    /// <summary>
    /// 单元格类
    /// </summary>
    class Cell
    {
        /// <summary>
        /// 行坐标
        /// </summary>
        public int X { get; set; }

        /// <summary>
        /// 列坐标
        /// </summary>
        public int Y { get; set; }

        /// <summary>
        /// 内容
        /// </summary>
        public string Content { get; set; }

        public Cell()
        {
        }

        public Cell(int x, int y, string content)
        {
            X = x;
            Y = y;
            Content = content;
        }
    }
}